{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Helpdesk Hard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "········\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@192.168.31.31:15432/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "shift = pd.read_sql_table('Shift', engine)\n",
    "staff = pd.read_sql_table('Staff', engine)\n",
    "issue = pd.read_sql_table('Issue', engine)\n",
    "shift_type = pd.read_sql_table('Shift_type', engine)\n",
    "level = pd.read_sql_table('Level', engine)\n",
    "customer = pd.read_sql_table('Customer', engine)\n",
    "caller = pd.read_sql_table('Caller', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11.\n",
    "Show the manager and number of calls received for each hour of the day on 2017-08-12\n",
    "\n",
    "```\n",
    "+---------+---------------+----+\n",
    "| Manager | Hr            | cc |\n",
    "+---------+---------------+----+\n",
    "| LB1     | 2017-08-12 08 |  6 |\n",
    "| LB1     | 2017-08-12 09 | 16 |\n",
    "| LB1     | 2017-08-12 10 | 11 |\n",
    "| LB1     | 2017-08-12 11 |  6 |\n",
    "| LB1     | 2017-08-12 12 |  8 |\n",
    "| LB1     | 2017-08-12 13 |  4 |\n",
    "| AE1     | 2017-08-12 14 | 12 |\n",
    "| AE1     | 2017-08-12 15 |  8 |\n",
    "| AE1     | 2017-08-12 16 |  8 |\n",
    "| AE1     | 2017-08-12 17 |  7 |\n",
    "| AE1     | 2017-08-12 19 |  5 |\n",
    "+---------+---------------+----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Manager</th>\n",
       "      <th>hr</th>\n",
       "      <th>Call_ref</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>LB1</td>\n",
       "      <td>2017-08-12 08</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>LB1</td>\n",
       "      <td>2017-08-12 09</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>LB1</td>\n",
       "      <td>2017-08-12 10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>LB1</td>\n",
       "      <td>2017-08-12 11</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>LB1</td>\n",
       "      <td>2017-08-12 12</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>LB1</td>\n",
       "      <td>2017-08-12 13</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AE1</td>\n",
       "      <td>2017-08-12 14</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AE1</td>\n",
       "      <td>2017-08-12 15</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AE1</td>\n",
       "      <td>2017-08-12 16</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AE1</td>\n",
       "      <td>2017-08-12 17</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AE1</td>\n",
       "      <td>2017-08-12 19</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Manager             hr  Call_ref\n",
       "5      LB1  2017-08-12 08         6\n",
       "6      LB1  2017-08-12 09        16\n",
       "7      LB1  2017-08-12 10        11\n",
       "8      LB1  2017-08-12 11         6\n",
       "9      LB1  2017-08-12 12         8\n",
       "10     LB1  2017-08-12 13         4\n",
       "0      AE1  2017-08-12 14        12\n",
       "1      AE1  2017-08-12 15         8\n",
       "2      AE1  2017-08-12 16         8\n",
       "3      AE1  2017-08-12 17         7\n",
       "4      AE1  2017-08-12 19         5"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(issue.assign(date_call=issue['Call_date'].dt.date)\n",
    " .assign(hr=issue['Call_date'].dt.strftime('%Y-%m-%d %H'))\n",
    " .astype({'date_call': 'datetime64[ns]'})\n",
    " .query('date_call==\"2017-08-12\"')\n",
    " .merge(shift, left_on=['Taken_by', 'date_call'], \n",
    "        right_on=['Operator', 'Shift_date'])\n",
    " [['Manager', 'hr', 'Call_ref']]\n",
    " .groupby(['Manager', 'hr'])\n",
    " .count().reset_index()\n",
    " .sort_values('hr'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.\n",
    "**80/20 rule. It is said that 80% of the calls are generated by 20% of the callers. Is this true? What percentage of calls are generated by the most active 20% of callers.**\n",
    "\n",
    "Note - Andrew has not managed to do this in one query - but he believes it is possible.\n",
    "\n",
    "```\n",
    "+---------+\n",
    "| t20pc   |\n",
    "+---------+\n",
    "| 32.2581 |\n",
    "+---------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>t20pc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32.258065</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       t20pc\n",
       "0  32.258065"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = (issue.groupby('Caller_id')['Caller_id'].count()\n",
    " .sort_values(ascending=False)[:int(issue['Caller_id'].nunique()*0.2)])\n",
    "pd.DataFrame({'t20pc': [100 * sum(a)/issue.shape[0]]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13.\n",
    "**Annoying customers. Customers who call in the last five minutes of a shift are annoying. Find the most active customer who has never been annoying.**\n",
    "\n",
    "```\n",
    "+--------------+------+\n",
    "| Company_name | abna |\n",
    "+--------------+------+\n",
    "| High and Co. |   20 |\n",
    "+--------------+------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Company_ref</th>\n",
       "      <th>Company_name</th>\n",
       "      <th>abna</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>146</td>\n",
       "      <td>High and Co.</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Company_ref  Company_name  abna\n",
       "42          146  High and Co.    20"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "annoy = (issue.assign(date_call=issue['Call_date'].dt.date)\n",
    "         .astype({'date_call': 'datetime64[ns]'})\n",
    "         .merge(shift, left_on=['Taken_by', 'date_call'], right_on=['Operator', 'Shift_date'])\n",
    "         .merge(shift_type, on='Shift_type')\n",
    "         .merge(caller, on='Caller_id')\n",
    "         .merge(customer, on='Company_ref')\n",
    "         [['Company_ref', 'Shift_date', 'End_time', 'Call_date']])\n",
    "annoy['Shift_end'] = (annoy['Shift_date'].astype(str).str.cat(annoy['End_time'], sep=' ')\n",
    "                      .astype('datetime64[ns]'))\n",
    "annoy = annoy.loc[(annoy['Shift_end']-annoy['Call_date'])/pd.Timedelta(minutes=1) <= 5, :]\n",
    "\n",
    "a = (issue.merge(caller, on='Caller_id')\n",
    "     .merge(customer, on='Company_ref'))\n",
    "\n",
    "(a.loc[~a['Company_ref'].isin(annoy['Company_ref'])]\n",
    " .groupby(['Company_ref', 'Company_name'], as_index=True)\n",
    " .agg(abna=('Caller_id', 'count'))\n",
    " .reset_index()\n",
    " .sort_values('abna', ascending=False)\n",
    " .iloc[:1, ])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "**Maximal usage. If every caller registered with a customer makes a call in one day then that customer has \"maximal usage\" of the service. List the maximal customers for 2017-08-13.**\n",
    "\n",
    "```\n",
    "+-------------------+--------------+-------------+\n",
    "| company_name      | caller_count | issue_count |\n",
    "+-------------------+--------------+-------------+\n",
    "| Askew Inc.        |            2 |           2 |\n",
    "| Bai Services      |            2 |           2 |\n",
    "| Dasher Services   |            3 |           3 |\n",
    "| High and Co.      |            5 |           5 |\n",
    "| Lady Retail       |            4 |           4 |\n",
    "| Packman Shipping  |            3 |           3 |\n",
    "| Pitiable Shipping |            2 |           2 |\n",
    "| Whale Shipping    |            2 |           2 |\n",
    "+-------------------+--------------+-------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Company_name</th>\n",
       "      <th>caller_count</th>\n",
       "      <th>issue_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Askew Inc.</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bai Services</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Dasher Services</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>High and Co.</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Lady Retail</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Packman Shipping</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Pitiable Shipping</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Whale Shipping</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Company_name  caller_count  issue_count\n",
       "1          Askew Inc.             2            2\n",
       "3        Bai Services             2            2\n",
       "11    Dasher Services             3            3\n",
       "20       High and Co.             5            5\n",
       "26        Lady Retail             4            4\n",
       "33   Packman Shipping             3            3\n",
       "36  Pitiable Shipping             2            2\n",
       "49     Whale Shipping             2            2"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iss = (issue.loc[(issue['Call_date'] >= '2017-08-13') & \n",
    "                 (issue['Call_date'] < '2017-08-14'), :]\n",
    " .merge(caller, on='Caller_id', how='right')\n",
    " .merge(customer, on='Company_ref', how='left')\n",
    " .groupby(['Caller_id', 'Company_ref', 'Company_name'], as_index=True)\n",
    " .agg(n=('Call_ref', 'count'))\n",
    " .reset_index())\n",
    "iss['iss'] = (iss['n'] > 0).astype(int)\n",
    "\n",
    "(iss.groupby(['Company_name']).agg(caller_count=('Company_ref', 'count'),\n",
    "                                   issue_count=('iss', 'sum'))\n",
    " .reset_index()\n",
    " .query('caller_count==issue_count')\n",
    " .sort_values('Company_name'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "**Consecutive calls occur when an operator deals with two callers within 10 minutes. Find the longest sequence of consecutive calls – give the name of the operator and the first and last call date in the sequence.**\n",
    "\n",
    "```\n",
    "+----------+---------------------+---------------------+-------+\n",
    "| taken_by | first_call          | last_call           | calls |\n",
    "+----------+---------------------+---------------------+-------+\n",
    "| AB1      | 2017-08-14 09:06:00 | 2017-08-14 10:17:00 |    24 |\n",
    "+----------+---------------------+---------------------+-------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Taken_by</th>\n",
       "      <th>grp</th>\n",
       "      <th>first_call</th>\n",
       "      <th>last_call</th>\n",
       "      <th>n_calls</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AB1</td>\n",
       "      <td>2</td>\n",
       "      <td>2017-08-14 09:06:00</td>\n",
       "      <td>2017-08-14 10:17:00</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Taken_by  grp          first_call           last_call  n_calls\n",
       "2      AB1    2 2017-08-14 09:06:00 2017-08-14 10:17:00       24"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = issue.copy()\n",
    "t['lag'] = (issue.sort_values(['Taken_by', 'Call_date'])\n",
    "            .groupby('Taken_by')['Call_date'].shift(1))\n",
    "t['flag'] = (t['Call_date'] - t['lag'] > pd.Timedelta(minutes=10)).astype(int)\n",
    "t['grp'] = t.groupby('Taken_by')['flag'].cumsum()\n",
    "t = (t.groupby(['Taken_by', 'grp']).agg(first_call=('Call_date', 'min'),\n",
    "                                       last_call=('Call_date', 'max'),\n",
    "                                       n_calls=('Caller_id', 'count'))\n",
    "     .reset_index())\n",
    "t.sort_values('n_calls', ascending=False).iloc[:1, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
